
package com.scalar.chiptrack.users.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.users.NotificationSetup;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

public final class NotificationSetupDAO implements Constants
{

    public static void saveNotificationSetupInfo (NotificationSetup notificationSetup) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        //Commented by KumaraSwamy On Sep 1st, 2004.
        //String engPullEmail = StringUtils.concatStrings (notificationSetup.getEngPullEmails(), Mailer.EMAIL_SEPARATOR);
        //String engReturnEmail = StringUtils.concatStrings (notificationSetup.getEngReturnEmails(), Mailer.EMAIL_SEPARATOR);
        String approvedSOEmails = StringUtils.concatStrings (notificationSetup.getApprovedSOEmails(), Mailer.EMAIL_SEPARATOR);
        String psEmail = StringUtils.concatStrings (notificationSetup.getPackingSlipEmails(), Mailer.EMAIL_SEPARATOR);
        String alEmail = StringUtils.concatStrings (notificationSetup.getAdaptorLogEmails(), Mailer.EMAIL_SEPARATOR);
        String soEmail = StringUtils.concatStrings (notificationSetup.getSalesOrderEmails(), Mailer.EMAIL_SEPARATOR);
        String sopEmail = StringUtils.concatStrings (notificationSetup.getSalesOrderPortingEmails(), Mailer.EMAIL_SEPARATOR);

        // BR, march 14, 2004
        String shipAlertEmails  = StringUtils.concatStrings (notificationSetup.getShipAlertEmails(), Mailer.EMAIL_SEPARATOR);
        String fabStageEmail    = StringUtils.concatStrings (notificationSetup.getFabStageIdEmails(), Mailer.EMAIL_SEPARATOR);
        String sortReleaseEmails = StringUtils.concatStrings (notificationSetup.getSortReleaseEmails(), Mailer.EMAIL_SEPARATOR);

        String programReleaseEmail  = StringUtils.concatStrings (notificationSetup.getProgramReleaseEmails(), Mailer.EMAIL_SEPARATOR);
        String assemblyReleaseEmail  = StringUtils.concatStrings (notificationSetup.getAssemblyReleaseEmails(), Mailer.EMAIL_SEPARATOR);
        String newLotEmail  = StringUtils.concatStrings ( notificationSetup.getNewLotEmails(), Mailer.EMAIL_SEPARATOR );
        String reportsEmail  = StringUtils.concatStrings ( notificationSetup.getReportsEmails(), Mailer.EMAIL_SEPARATOR );
        try {
            conn = DataSource.getConnection();

            // TEMPORARY
            // LATER ON -- UPDATE/INSERT A ROW BASED ON CLIENT-ID
            pstmt = conn.prepareStatement ("delete from Notification_Emails_Table");
            pstmt.execute();
            pstmt.close();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("users.notificationSetup.saveNotificationSetupInfo"));

            //pstmt.setString (1, engPullEmail);
            //pstmt.setString (2, engReturnEmail);
            pstmt.setString (1, approvedSOEmails);
            pstmt.setString (2, psEmail);
            pstmt.setString (3, alEmail);
            pstmt.setString (4, shipAlertEmails );  // BR, march 14, 2004
            pstmt.setString (5, soEmail);
            pstmt.setString (6, sopEmail);
            pstmt.setString (7, fabStageEmail );

            pstmt.setString (8, sortReleaseEmails );
            pstmt.setString (9, programReleaseEmail );
            pstmt.setString (10, assemblyReleaseEmail );
            pstmt.setString (11, newLotEmail );
            pstmt.setString (12, reportsEmail );


            pstmt.executeUpdate();

            //System.out.println ("engPullEmail = " + engPullEmail + ", engReturnEmail = " + engReturnEmail + ", approvedSOEmails = " + approvedSOEmails + ", psEmail = " + psEmail);
            success = true;
        }

        catch (Exception sqle) {
            System.out.println ("Could not save Notification Emails into database - " + sqle);
            throw new ScalarException ("Could not save Notification Emails into database", sqle);
        }

        finally {

            try {
                if (conn != null) {
                    if (success == true) { conn.commit(); }
                    else { conn.rollback(); }
                }
            }
            catch (SQLException ignore) {}


            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }


    public static NotificationSetup loadNotificationSetupInfo() throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        NotificationSetup notificationSetup = null;

        try{
            conn = DataSource.getConnection();
            ps = conn.prepareStatement (SqlQueryMappings.get ("users.notificationSetup.loadNotificationSetupInfo"));
            rs = ps.executeQuery();

            if (rs.next()) {
                //Commented By KumaraSwamy On Sep 1st, 2004.
                /*notificationSetup = new NotificationSetup (StringUtils.toStringArray (rs.getString ("Engineering_Pull_Emails"), Mailer.EMAIL_SEPARATOR),
                                                             StringUtils.toStringArray (rs.getString ("Engineering_Return_Emails"), Mailer.EMAIL_SEPARATOR),
                                                             StringUtils.toStringArray (rs.getString ("Approved_Sales_Order_Emails"), Mailer.EMAIL_SEPARATOR),
                                                             StringUtils.toStringArray (rs.getString ("Packing_Slip_Emails"), Mailer.EMAIL_SEPARATOR),
                                                             StringUtils.toStringArray (rs.getString ("Adaptor_Log_Emails"), Mailer.EMAIL_SEPARATOR),
                                                             StringUtils.toStringArray (rs.getString ("Ship_Alert_Emails"), Mailer.EMAIL_SEPARATOR) );*/
                //Added by KumaraSwamy On Sep 1st, 2004.
                /*notificationSetup = new NotificationSetup (StringUtils.toStringArray (rs.getString ("Approved_Sales_Order_Emails"), Mailer.EMAIL_SEPARATOR),
                                                           StringUtils.toStringArray (rs.getString ("Packing_Slip_Emails"), Mailer.EMAIL_SEPARATOR),
                                                           StringUtils.toStringArray (rs.getString ("Adaptor_Log_Emails"), Mailer.EMAIL_SEPARATOR),
                                                           StringUtils.toStringArray (rs.getString ("Ship_Alert_Emails"), Mailer.EMAIL_SEPARATOR),
                                                           StringUtils.toStringArray (rs.getString ("Sales_Order_Emails"), Mailer.EMAIL_SEPARATOR),
                                                           StringUtils.toStringArray (rs.getString ("Sales_Order_Porting_Emails"), Mailer.EMAIL_SEPARATOR) );*/

                //Added by sudhakar on 7th June 2005
                notificationSetup = new NotificationSetup (StringUtils.toStringArray (rs.getString ("Approved_Sales_Order_Emails"), Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray (rs.getString ("Packing_Slip_Emails"), Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray (rs.getString ("Adaptor_Log_Emails"), Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray (rs.getString ("Ship_Alert_Emails"), Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray (rs.getString ("Sales_Order_Emails"), Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray (rs.getString ("Sales_Order_Porting_Emails"), Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray( rs.getString ("Fab_Stage_Emails"),Mailer.EMAIL_SEPARATOR),
                                                                          StringUtils.toStringArray( rs.getString ("Sort_Release_Emails"),Mailer.EMAIL_SEPARATOR));
                String programReleaseEmails = rs.getString ( "Program_Release_Emails" );
                notificationSetup.setProgramReleaseEmails( StringUtils.toStringArray( programReleaseEmails, Mailer.EMAIL_SEPARATOR ) );
                String assemblyReleaseEmails = rs.getString ( "Assembly_Release_Emails" );
                notificationSetup.setAssemblyReleaseEmails( StringUtils.toStringArray( assemblyReleaseEmails, Mailer.EMAIL_SEPARATOR ) );
                String newLotEmails = rs.getString ( "Lot_Flow_Emails" );
                notificationSetup.setNewLotEmails( StringUtils.toStringArray( newLotEmails, Mailer.EMAIL_SEPARATOR ) );
                String reportsEmails = rs.getString ( "Reports_Emails" );
                notificationSetup.setReportsEmails( StringUtils.toStringArray( reportsEmails, Mailer.EMAIL_SEPARATOR ) );

            }
        }

        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not load Notification Setup information from database", se);
        }

        finally{
            if (rs != null){
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return notificationSetup;
    }



    public static String loadAdaptorNotificationSubscriberEmails() throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String emails = null;

        try{
            conn = DataSource.getConnection();
            ps = conn.prepareStatement (SqlQueryMappings.get ("users.notificationSetup.loadAdaptorNotificationSubscriberEmails"));
            rs = ps.executeQuery();

            if (rs.next()) {
                emails = rs.getString ("Adaptor_Log_Emails");
            }
        }

        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not load Adaptor Notification Subscribers emails from database", se);
        }

        finally{
            if (rs != null){
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return emails;
    }


    // Added by Balaram, Sep 15, 2003
    public static String[] loadEmailsByType( int type ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = null;
        String query = null;

        switch( type )
        {
            case 0:
                query = "operations.packingslip.loadVendorEmailsForPackingSlip";
                break;

            case 1:
                query = "sales.salesOrder.loadReceipientListForApprovedSalesOrders";
                break;

            case 2:
                query = "users.notificationSetup.loadAdaptorNotificationSubscriberEmails";
                break;

            case 3:
                query = "operations.packingslip.loadShipAlertEmails";
                break;

            case 4:
                query = "sales.salesOrder.loadReceipientListForSalesOrders";
                break;

            case 5:
                query = "sales.salesOrder.loadSalesOrderPortingNotificationEmails";
                break;

            case 6:
                query = "operations.fabstages.loadFabStageNotificationEmail";
                break;
            case 7:
                query = "operations.sortRelease.loadSortReleaseNotificationEmail";
                break;
            case 8:
                query = "operations.programRelease.loadProgramReleaseNotificationEmail";
                break;
            case 9:
                query = "operations.assemblyRelease.loadAssemblyReleaseNotificationEmail";
                break;
            case 10:
                query = "operations.newlot.loadNewLotNotificationEmail";
                break;
            case 11:
                query = "operations.reports.loadReportsAlertNotificationEmail";
                break;
            default:
                break;
        }

        if( query != null )
        {
            try
            {
                conn = DataSource.getConnection();

                pstmt = conn.prepareStatement( SqlQueryMappings.get( query ) );
                rset = pstmt.executeQuery();

                if( rset.next() )
                {
                    emails = rset.getString( 1 );
                }
            }
            catch( Exception e )
            {
                throw new ScalarException( "Could not retrieve email list from database", e );
            }
            finally
            {
                if( rset != null )
                {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if( pstmt != null )
                {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
                if( conn != null )
                {
                    try { conn.close(); } catch (SQLException ignore) {}
                }
            }
        }

        String[] emailArray = null;

        if( emails != null )
        {
            emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

            if( ( emailArray != null ) && ( emailArray.length > 0 ) )
            {
                Arrays.sort( emailArray );
            }
        }

        return emailArray;
    }
}